package com.yuhuihui.common.utils.excel.read;

import com.yuhuihui.common.utils.ConverterUtil;
import com.yuhuihui.common.utils.OptionalUtil;
import com.yuhuihui.common.utils.excel.read.convert.CellTypeConvertParameter;
import com.yuhuihui.common.utils.excel.read.parameters.ExcelReadCellParameter;
import com.yuhuihui.common.utils.excel.read.parameters.ExcelReadMapParameter;
import com.yuhuihui.common.utils.excel.read.parameters.ExcelReadObjectParameter;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel导入
 *
 * @author yuhh
 * @date 2022-08-22 16:19:56
 */
public class ExcelRead {

    private static final Logger logger = LoggerFactory.getLogger(ExcelRead.class);

    /**
     * 执行读取excel，并返回实体类列表
     *
     * @param parameter 读取excel参数
     * @return {@link List }<{@link T }> 读取后的数据
     * @author yuhh
     * @date 2022-08-22 16:34:27
     */
    protected static <T> List<T> doReadResultObject(ExcelReadObjectParameter<T> parameter){
        Sheet sheet;
        List<T> results = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(parameter.getInputStream());
            for (int sheetNum = parameter.getStartSheet(), sheetCount = OptionalUtil.getNotNull(parameter.getEndSheet(), workbook::getNumberOfSheets); sheetNum < sheetCount; sheetNum ++){
                 sheet = workbook.getSheetAt(sheetNum);
                 results.addAll(readSheetResultObject(sheet, parameter));
            }
        } catch (Exception e) {
            logger.error("导入excel，并返回实体类列表出现异常", e);
        }
        return results;
    }

    /**
     * 执行读取excel，并返回map列表
     *
     * @param parameter 读取excel参数
     * @return {@link List }<{@link T }> 读取后的数据
     * @author yuhh
     * @date 2022-08-22 16:34:32
     */
    protected static <T extends Map<String, Object>> List<T> doReadResultMap(ExcelReadMapParameter<T> parameter){
        Sheet sheet;
        List<T> results = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(parameter.getInputStream());
            for (int sheetNum = parameter.getStartSheet(), sheetCount = OptionalUtil.getNotNull(parameter.getEndSheet(), workbook::getNumberOfSheets); sheetNum < sheetCount; sheetNum ++){
                sheet = workbook.getSheetAt(sheetNum);
                results.addAll(readSheetResultMap(sheet, parameter));
            }
        } catch (Exception e) {
            logger.error("导入excel，并返回map列表出现异常", e);
        }
        return results;
    }

    /**
     * 读取工作区，并返回实体类列表
     *
     * @param sheet     工作区
     * @param parameter excel导入参数
     * @return {@link List }<{@link T }> 解析后实体类列表
     * @throws Exception 异常
     * @author yuhh
     * @date 2022-08-22 16:34:38
     */
    private static <T> List<T> readSheetResultObject(Sheet sheet, ExcelReadObjectParameter<T> parameter) throws Exception {
        int rowCount = OptionalUtil.getNotNull(parameter.getEndRow(), sheet::getPhysicalNumberOfRows);
        int startRow = parameter.getStartRow();
        List<T> results = new ArrayList<>();
        T t;
        for (int rowNum = startRow; rowNum < rowCount; rowNum ++){
            t = readRowResultObject(sheet.getRow(rowNum), parameter);
            // 校验行数据
            if (parameter.getVerifyRow().verifyRow(t)){
                results.add(t);
            }
        }
        return results;
    }

    /**
     * 读取工作区，并返回map列表
     *
     * @param sheet     工作区
     * @param parameter excel导入参数
     * @return {@link List }<{@link T }> 解析后map列表
     * @throws Exception 异常
     * @author yuhh
     * @date 2022-08-22 16:34:44
     */
    private static <T extends Map<String, Object>> List<T> readSheetResultMap(Sheet sheet, ExcelReadMapParameter<T> parameter) throws Exception {
        int rowCount = OptionalUtil.getNotNull(parameter.getEndRow(), sheet::getPhysicalNumberOfRows);
        int startRow = parameter.getStartRow();
        List<T> results = new ArrayList<>();
        T map;
        for (int rowNum = startRow; rowNum < rowCount; rowNum ++){
            map = readRowResultMap(sheet.getRow(rowNum), parameter);
            // 校验行数据
            if (parameter.getVerifyRow().verifyRow(map)){
                results.add(map);
            }
        }
        return results;
    }

    /**
     * 读取行，并返回实体类
     *
     * @param row       行数据
     * @param parameter excel导入参数
     * @return {@link T } 解析后的实体类
     * @throws Exception 异常
     * @author yuhh
     * @date 2022-08-22 16:34:50
     */
    private static <T> T readRowResultObject(Row row, ExcelReadObjectParameter<T> parameter) throws Exception {
        int cellStartNum = parameter.getStartCell();

        int cellCount = parameter.getEndCell() == null? row.getLastCellNum(): parameter.getEndCell() - cellStartNum;

        Row newRow = deleteCell(parameter.getDeleteCellNums(), row, cellStartNum, cellCount + cellStartNum);

        cellCount = newRow.getLastCellNum();

        String[] fieldCodes = parameter.getFieldCodes();

        calibrationRow(cellCount, fieldCodes);

        Class<T> cls = parameter.getCls();
        Map<String, Map<String, Object>> replaceInfo = parameter.getReplaceInfo();
        Map<String, Object> defaultValue = parameter.getDefaultValue();
        T t = cls.newInstance();
        String fieldCode;
        Object value;
        PropertyDescriptor pd;
        Method method;
        for (int cellNum = 0; cellNum < cellCount; cellNum ++){
            fieldCode = fieldCodes[cellNum];
            // 根据code获取相应属性的set/get方法
            pd = new PropertyDescriptor(fieldCode, cls);
            // 获取set方法
            method = pd.getWriteMethod();
            // 获取相应单元格的值
            value = getCellValue(new ExcelReadCellParameter(newRow.getCell(cellNum), fieldCode,
                    replaceInfo, defaultValue, parameter.getCellTypeConvertFactory()));

            method.invoke(t, ConverterUtil.convert(pd.getPropertyType(), value));
        }
        return t;
    }

    /**
     * 读取行，并返回map
     *
     * @param row       行数据
     * @param parameter excel导入参数
     * @return {@link T } map
     * @throws Exception 异常
     * @author yuhh
     * @date 2022-08-22 16:35:21
     */
    private static <T extends Map<String, Object>> T readRowResultMap(Row row, ExcelReadMapParameter<T> parameter) throws Exception {
        int cellStartNum = parameter.getStartCell();

        int cellCount = parameter.getEndCell() == null? row.getLastCellNum(): parameter.getEndCell() - cellStartNum;

        Row newRow = deleteCell(parameter.getDeleteCellNums(), row, cellStartNum, cellCount + cellStartNum);

        cellCount = newRow.getLastCellNum();

        String[] fieldCodes = parameter.getFieldCodes();

        calibrationRow(cellCount, fieldCodes);

        @SuppressWarnings("unchecked")
        T result = (T) new HashMap<String, Object>(cellCount);

        Map<String, Map<String, Object>> replaceInfo = parameter.getReplaceInfo();
        Map<String, Object> defaultValue = parameter.getDefaultValue();
        String fieldCode;
        for (int cellNum = 0; cellNum < cellCount; cellNum ++){
            fieldCode = fieldCodes[cellNum];
            // 获取相应单元格的值
            Object value = getCellValue(new ExcelReadCellParameter(newRow.getCell(cellNum), fieldCode,
                    replaceInfo, defaultValue, parameter.getCellTypeConvertFactory()));

            result.put(fieldCode, value);
        }
        return result;
    }

    /**
     * 获取单元格值
     *
     * @param parameter 单元格参数
     * @return {@link Object } 单元格值
     * @throws ParseException 异常
     * @author yuhh
     * @date 2022-08-22 16:35:30
     */
    private static Object getCellValue(ExcelReadCellParameter parameter) throws ParseException {
        Object value = null;
        Cell cell = parameter.getCell();
        String fieldCode = parameter.getFieldCode();
        if (cell != null){
            value = parameter.getCellTypeConvertFactory().creator(cell.getCellType().ordinal())
                    .cellTypeConvert(new CellTypeConvertParameter(cell));
        }

        // 判断值为null的字段是否有默认值
        if (value == null && !CollectionUtils.isEmpty(parameter.getDefaultValueKeys()) && parameter.getDefaultValueKeys().contains(fieldCode)){
            value = parameter.getDefaultValue().get(fieldCode);
        }

        // 判断给字段是否需要替换
        if (!CollectionUtils.isEmpty(parameter.getReplaceInfoKeys()) && parameter.getReplaceInfoKeys().contains(fieldCode) && value != null){
            value = parameter.getReplaceInfo().get(fieldCode).get(String.valueOf(value));
        }

       return value;
    }

    /**
     * 根据指定的删除单元格序号删除单元格
     *
     * @param deleteCellNums 需要删除的列下标
     * @param row            行数据
     * @param startIndex     开始下标
     * @param endIndex       结束下标
     * @return {@link Row } 删除指定列之后的行数据
     * @author yuhh
     * @date 2022-08-22 16:35:41
     */
    private static Row deleteCell(List<Integer> deleteCellNums, Row row, int startIndex, int endIndex) {
        if (!CollectionUtils.isEmpty(deleteCellNums)) {
            Sheet sheet = row.getSheet();
            Row newRow = sheet.createRow(row.getRowNum());
            Cell nowCell, newCell;
            int newCellIndex = 0;
            for (int i = startIndex; i < endIndex; i ++){
                if (!deleteCellNums.contains(i)){
                    nowCell = row.getCell(i);
                    if (nowCell == null){
                        newCellIndex ++;
                        continue;
                    }
                    newCell = newRow.createCell(newCellIndex, nowCell.getCellType());
                    cloneCell(newCell, nowCell);
                    newCellIndex ++;
                }
            }
            return newRow;
        }
        return row;
    }

    /**
     * 克隆cell
     *
     * @param newCell 克隆后的cell
     * @param oldCell 需要被克隆的cell
     * @author yuhh
     * @date 2022-08-22 16:35:46
     */
    private static void cloneCell(Cell newCell, Cell oldCell) {
        newCell.setCellComment(oldCell.getCellComment());
        newCell.setCellStyle(oldCell.getCellStyle());

        switch (newCell.getCellType()){
            case BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break;
            case NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break;
            case STRING: newCell.setCellValue(oldCell.getStringCellValue()); break;
            case ERROR: newCell.setCellValue(oldCell.getErrorCellValue()); break;
            case FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break;
            default: newCell.setCellValue(""); break;
        }
    }

    /**
     * 校验row的数据准确性
     *
     * @param cellCount  单元格数量
     * @param fieldCodes 字段code列表
     * @author yuhh
     * @date 2022-08-22 16:35:49
     */
    private static void calibrationRow(int cellCount, String[] fieldCodes){
        Assert.isTrue(fieldCodes.length == cellCount,
                "fieldCodes的长度和单元格长度不同，fieldCodes长度：" + fieldCodes.length + "，单元格长度：" + cellCount);
    }
}
